package test.study.db.sql.jsqlparser;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.junit.Test;

import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class SqlUtil {

    @SneakyThrows
    @Test
    public void insert_test() {
        //
        InputStream is = ResourceUtil.getStream("dml/insert.txt");
        //
        List<String> lines = IoUtil.readLines(is, Charset.defaultCharset(), new ArrayList<>());

        //
        String sql = "INSERT INTO download_excel_config (creator, business_type, excel_type, header_config, host, service_name, method_url, page_size, excel_name, sheet_name, enable_flag, call_type, delete_flag) VALUES ";
        //
        List<String> str = CollectionUtil.map(lines, e -> {
            String[] array = StrUtil.subBetweenAll(e, "(", ")");
            String values = ArrayUtil.get(array, 1);
            List<String> valueLt = StrUtil.splitTrim(values, ", ");
            valueLt = CollectionUtil.sub(valueLt, 1, valueLt.size() - 2);
            return StrUtil.format("({}, {},{}, {}, {}, {}, {}, {}, {}, {}, {}, {}, {});", valueLt.toArray(new String[]{}));
        }, true);

        str.forEach(e -> System.out.println(sql + e));
    }

    @SneakyThrows
    @Test
    public void test() {
        //
        InputStream is = ResourceUtil.getStream("dml/insert.txt");
        List<String> lines = IoUtil.readLines(is, Charset.forName("utf-8"), new ArrayList<>());

        //
        for (String line : lines) {
            //
            Insert insert = (Insert) CCJSqlParserUtil.parse(line);

            //
            ExpressionList<Column> columns = insert.getColumns();
            CollectionUtil.filter(columns, e -> {
                if (StrUtil.equalsAny(e.getColumnName(), "id", "create_time", "update_time")) {
                    return false;
                }
                return true;
            });
            //
            ExpressionList<Expression> expLt = (ExpressionList<Expression>) insert.getValues().getExpressions();
            expLt.remove(0);
            expLt.remove(expLt.size() - 1);
            expLt.remove(expLt.size() - 1);

//        log.info("{};", SqlFormatter.format(insert.toString()));
            System.out.println(insert + ";");
        }
    }

    @SneakyThrows
    @Test
    public void update_test() {
        //
        InputStream is = ResourceUtil.getStream("dml/update.txt");
        List<String> lines = IoUtil.readLines(is, Charset.forName("utf-8"), new ArrayList<>());

        //
        for (String line : lines) {
            //
            Update update = (Update) CCJSqlParserUtil.parse(line);

            //
            List<UpdateSet> setLt = update.getUpdateSets();
            //
            CollectionUtil.filter(setLt, e -> {
                String name = e.getColumns().get(0).getColumnName();
                if (StrUtil.equals(name, "header_config")) {
                    return true;
                }
                return false;
            });
            //
            System.out.println(update + ";");
        }
    }
}
